﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using CoppyDatabase.Commons;
using System.IO;

using MySql.Data.MySqlClient;

namespace CoppyDatabase
{
    public partial class frmLogon : Form
    {
       

        #region Khai báo biến SQL
        private  static SqlConnection sql_con;
        private SqlCommand sql_cmd;
        private SqlDataAdapter sql_Ad;
        private DataSet DS = new DataSet();
        private DataTable DT = new DataTable();

        private MySqlConnection MySql_con;
        private MySqlCommand MySql_cmd;
        private MySqlDataAdapter MySql_Ad;
        private DataSet MySql_DS = new DataSet();
        private DataTable MySql_DT = new DataTable();
        

        #endregion
        private string _StrMsg;
        public string gs_Msg
        {
            get { return _StrMsg; }
            set { _StrMsg = value; }
        }
        private string _TypeConvert;
        public string gs_TypeConvert
        {
            get { return _TypeConvert; }
            set { _TypeConvert = value; }
        }
        public frmLogon()
        {
            InitializeComponent();
            //clsExecuteQuery = new clsExecuteQuery();
        }


        private void frmLogon_Load(object sender, EventArgs e)
        {
            string cui = "''";
            string chuar = "specially when it rains, but don't let that deter you";
            string daR = chuar.Replace("'", cui);
            FileInfo checkFile = new FileInfo(Common.GetPath_SqlConnection());

            if (checkFile.Exists)
            {
                StreamReader readerSql_Connection = File.OpenText(Common.GetPath_SqlConnection());
                StreamReader readerMySql_Connection = File.OpenText(Common.GetPath_MySqlConnection());
                string SeverName = "", DataBaseName = "", UserName = "", Pass = "";
                try
                {
                    #region read data file .txt
                        string strAllFile_SQL = readerSql_Connection.ReadToEnd(); // doc toan bo data trong file.txt
                        string[] arrLines_SQL = strAllFile_SQL.Split(new string[] { ";", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries); // split ky tu [\r\n] va convert sang 1 Mang
                        if (strAllFile_SQL != null)
                        {
                            string strSever_Port = arrLines_SQL[0];
                            string strSever = string.Empty;
                            string strPort = string.Empty;
                            if (strSever_Port.Contains(","))
                            {
                                strSever = strSever_Port.Split(',')[0];
                                strPort = strSever_Port.Split(',')[1];
                            }
                            else
                            {
                                strSever = strSever_Port;
                            }
                            txtSeverName.Text = strSever; // dong 1
                            txtPort.Text = strPort; // dong 2
                            txtDBName.Text = arrLines_SQL[1]; // dong 3
                            if (arrLines_SQL.Count() > 2)
                            {
                                txtUserName.Text = arrLines_SQL[2]; // dong 4
                                txtPass.Text = arrLines_SQL[3]; // dong 5
                            }
                        }
                        readerSql_Connection.Close();
                        readerSql_Connection.Dispose();
                    // get data file MySql
                        string strAllFile_MySQL = readerMySql_Connection.ReadToEnd(); // doc toan bo data trong file.txt
                        string[] arrLines_MySQL = strAllFile_MySQL.Split(new string[] { ";", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries); // split ky tu [\r\n] va convert sang 1 Mang
                        if (strAllFile_MySQL != null)
                        {
                            txtSeverName2.Text  = arrLines_MySQL[0]; // dong 1
                            txtPort2.Text  = arrLines_MySQL[1]; // dong 2
                            txtDBName2.Text = arrLines_MySQL[2]; // dong 3
                            txtUserName2.Text = arrLines_MySQL[3]; // dong 4
                            txtPass2.Text = arrLines_MySQL[4]; // dong 5
                        }

                        readerMySql_Connection.Close();
                        readerMySql_Connection.Dispose();
                    #endregion

                    //if (Common.IsNullOrEmpty(UserName) || Common.IsNullOrEmpty(Pass))
                    //{
                    //    sql_con = new SqlConnection(@"Server=" + SeverName + "; Database=" + DataBaseName + "; Trusted_Connection=true; Integrated Security=True");
                    //}
                    //else
                    //{
                    //    sql_con = new SqlConnection(@"Server=" + SeverName + "; Database=" + DataBaseName + ";User Id=" + UserName + ";Password = " + Pass + ";");
                    //}



                    //if (Extension.CanOpen_Sql(sql_con))
                    //{
                    //    frmCopyData obj = new frmCopyData();
                    //    obj.ShowDialog();
                    //    this.Visible = true;
                    //    this.Close();

                    //}
                }
                catch (Exception ex)
                {

                }
            }
        }

        private void btnLogon_Click(object sender, EventArgs e)
        {
            gs_Msg = string.Empty;
            //validation check Convert Type
            if (ConvertType.Checked == false && ConvertType2.Checked == false)
            {
                MessageBox.Show("Vui lòng chọn loại muốn convert !");
                return;
            }
            else
            {
                if (ConvertType.Checked == true)
                {
                    gs_TypeConvert = "Convert Sql to MySql";
                }
                if (ConvertType2.Checked == true)
                {
                    gs_TypeConvert = "Convert MySql to Sql";
                }
                
            }


            string SeverName = txtSeverName.Text.Trim();
            string Port = txtPort.Text.Trim();
            string DataBaseName = txtDBName.Text.Trim();
            string UserName = txtUserName.Text.Trim();
            string Pass = txtPass.Text.Trim();

            string SeverName2 = txtSeverName2.Text.Trim();
            string Port2 = txtPort2.Text.Trim();
            string DataBaseName2 = txtDBName2.Text.Trim();
            //string DataBaseName2 = "default";
            string UserName2 = txtUserName2.Text.Trim();
            string Pass2 = txtPass2.Text.Trim();



            //check connection SQL
            if (!CheckConnection_SQL(SeverName, Port, DataBaseName, UserName, Pass))
            {
                if (gs_Msg.Length > 0)
                {
                    MessageBox.Show(gs_Msg);
                }
                else
                {
                    MessageBox.Show("Kết Nối DataBase SQL Sever Không Thành Công \n Vui Lòng Kiểm Tra Lại SeverName, DatabaseName, UserName, Password");
                }
                return;
            }
            //check connection MySQL
            if (!CheckConnection_MySQL(SeverName2, Port2, DataBaseName2, UserName2, Pass2))
            {
                if (gs_Msg.Length > 0)
                {
                    MessageBox.Show(gs_Msg);
                }
                else
                {
                    MessageBox.Show("Kết Nối DataBase MySQL Không Thành Công \n Vui Lòng Kiểm Tra Lại SeverName, DatabaseName, UserName, Password");
                }
                return;
            }
            MessageBox.Show("Kết Nối DataBase Thành Công");

            frmCopyData obj = new frmCopyData();
            
            obj.gsConnection_Sql = sql_con;
            obj.gsConnection_MySql = MySql_con;
            obj.gs_ConvertType = gs_TypeConvert;
            this.Visible = true;
            obj.ShowDialog();
            this.Close();

        }
        public Boolean CheckConnection_SQL(string strSeverName, string strPort, string strDataBaseName, string strUserName, string strPass)
        {
           

            try
            {
                if (gs_TypeConvert == "Convert MySql to Sql")
                {
                    SqlConnection connection;
                    if (!Common.IsNullOrEmpty(strPort))
                    {
                        strSeverName = strSeverName + "," + strPort;
                    }
                    if (Common.IsNullOrEmpty(strUserName) || Common.IsNullOrEmpty(strPass))
                    {
                        connection = new SqlConnection(@"Server=" + strSeverName + "; Trusted_Connection=true; Integrated Security=True");
                    }
                    else
                    {
                        connection = new SqlConnection("Data Source=" + strSeverName + ";Persist Security Info=True;User ID=" + strUserName + ";Password=" + strPass + ";MultipleActiveResultSets=True");
                    }

                   // SqlConnection connection = new SqlConnection("Data Source=" + strSeverName + "; Port=" + strPort + ";UserId=" + strUserName + ";PWD=" + strPass + ";");
                    string strCheckExist = "SELECT name FROM master.sys.databases WHERE name = '" + strDataBaseName + "'";
                    DataTable dtBaseExist = new DataTable();
                    dtBaseExist = clsExecuteQuery.SelectSql_ReturnTable(connection, strCheckExist);
                    if (dtBaseExist.Rows.Count > 0)
                    {
                        gs_Msg = "Can't create database '" + strDataBaseName + "'; database exists";
                        return false;
                    }
                    else
                    {
                        SqlCommand command = new SqlCommand("CREATE DATABASE " + strDataBaseName + ";", connection);
                        connection.Open();
                        command.ExecuteNonQuery();
                        connection.Close();

                        if (!Common.IsNullOrEmpty(strPort))
                        {
                            strSeverName = strSeverName + "," + strPort;
                        }
                        if (Common.IsNullOrEmpty(strUserName) || Common.IsNullOrEmpty(strPass))
                        {
                            sql_con = new SqlConnection(@"Server=" + strSeverName + "; Database=" + strDataBaseName + "; Trusted_Connection=true; Integrated Security=True");
                        }
                        else
                        {
                            sql_con = new SqlConnection("Data Source=" + strSeverName + ";Initial Catalog=" + strDataBaseName + ";Persist Security Info=True;User ID=" + strUserName + ";Password=" + strPass + ";MultipleActiveResultSets=True");
                        }
                        if (clsExecuteQuery.CanOpen_Sql(sql_con) == true)
                        {
                            string Data = strSeverName + Environment.NewLine + strDataBaseName + Environment.NewLine + strUserName + Environment.NewLine + strPass;
                            Common.WriterData(Common.GetPath_SqlConnection(), Data);
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                }
                else
                {
                    if (!Common.IsNullOrEmpty(strPort))
                    {
                        strSeverName = strSeverName + "," + strPort;
                    }
                    if (Common.IsNullOrEmpty(strUserName) || Common.IsNullOrEmpty(strPass))
                    {
                        sql_con = new SqlConnection(@"Server=" + strSeverName + "; Database=" + strDataBaseName + "; Trusted_Connection=true; Integrated Security=True");
                    }
                    else
                    {
                        sql_con = new SqlConnection("Data Source=" + strSeverName + ";Initial Catalog=" + strDataBaseName + ";Persist Security Info=True;User ID=" + strUserName + ";Password=" + strPass + ";MultipleActiveResultSets=True");
                    }
                    if (clsExecuteQuery.CanOpen_Sql(sql_con) == true)
                    {
                        string Data = strSeverName + Environment.NewLine + strDataBaseName + Environment.NewLine + strUserName + Environment.NewLine + strPass;
                        Common.WriterData(Common.GetPath_SqlConnection(), Data);
                        return true;
                    }
                    else
                    {
                        return false;
                    }

                }
            }
            catch (Exception ex)
            {
                
                if (!Common.IsNullOrEmpty(MySql_con))
                { 
                    MySql_con.Close();
                }
                if (!Common.IsNullOrEmpty(sql_con))
                {
                    sql_con.Close();
                }
                gs_Msg = ex.Message.ToString();
                return false;
               
            }
        }
        public Boolean CheckConnection_MySQL(string strSeverName, string strPort, string strDataBaseName, string strUserName, string strPass)
        {
            try
            {
                if (gs_TypeConvert == "Convert Sql to MySql")
                {
                    MySqlConnection connection = new MySqlConnection("Data Source=" + strSeverName + "; Port=" + strPort + ";UserId=" + strUserName + ";PWD=" + strPass + ";");
                    string strCheckExist = "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '" + strDataBaseName + "'";
                    DataTable dtBaseExist = new DataTable();
                    dtBaseExist = clsExecuteQuery.SelectMySql_ReturnTable(connection, strCheckExist);
                    if (dtBaseExist.Rows.Count > 0)
                    {
                        gs_Msg = "Can't create database '" + strDataBaseName + "'; database exists";
                        return false;
                    }
                    else
                    {
                        MySqlCommand command = new MySqlCommand("CREATE DATABASE " + strDataBaseName + ";", connection);
                        connection.Open();
                        command.ExecuteNonQuery();
                        connection.Close();

                        MySql_con = new MySqlConnection(@"Server=" + strSeverName + "; Port=" + strPort + "; Database=" + strDataBaseName + "; Uid=" + strUserName + "; Pwd='" + strPass + "'; charSet=utf8;");
                        if (clsExecuteQuery.CanOpen_MySql(MySql_con) == true)
                        {
                            string Data = strSeverName + Environment.NewLine + strPort + Environment.NewLine + strDataBaseName + Environment.NewLine + strUserName + Environment.NewLine + strPass;
                            Common.WriterData(Common.GetPath_MySqlConnection(), Data);
                        }
                        return true;
                    }
                }
                else
                {
                    MySql_con = new MySqlConnection(@"Server=" + strSeverName + "; Port=" + strPort + "; Database=" + strDataBaseName + "; Uid=" + strUserName + "; Pwd='" + strPass + "'; charSet=utf8;");
                    if (clsExecuteQuery.CanOpen_MySql(MySql_con) == true)
                    {
                        string Data = strSeverName + Environment.NewLine + strPort + Environment.NewLine + strDataBaseName + Environment.NewLine + strUserName + Environment.NewLine + strPass;
                        Common.WriterData(Common.GetPath_MySqlConnection(), Data);
                        return true;
                    }else
                        return false;
                    
                }
            }
            catch (Exception ex)
            {
                if (!Common.IsNullOrEmpty(MySql_con))
                {
                    MySql_con.Close();
                }
                if (!Common.IsNullOrEmpty(sql_con))
                {
                    sql_con.Close();
                }
                gs_Msg = ex.Message.ToString();
                return false;
                
            }
        }

        private void ConvertType_CheckedChanged(object sender, EventArgs e)
        {
            lblDataBase2.Text = "Create Database Name:";
            lblDataBase.Text = "Database Name:";
        }

        private void ConvertType2_CheckedChanged(object sender, EventArgs e)
        {
            lblDataBase2.Text = "Database Name:";
            lblDataBase.Text = "Create Database Name:";
        }

    }
}
